﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using MM.Entities;

namespace MM.Business
{
    public class clsPHIEU_DAT_HANG
    {
        public clsPHIEU_DAT_HANG(string sConn)
        {
            SqlHelper.ConnectionString = sConn;
        }

        #region Private methods
        private PHIEU_DAT_HANG GetDataRow(DataRow row)
        {
            PHIEU_DAT_HANG objPHIEU_DAT_HANG = new PHIEU_DAT_HANG();
            try
            {
                if (row["POThamChieuSAP"] != DBNull.Value)
                    objPHIEU_DAT_HANG.POThamChieuSAP = row["POThamChieuSAP"].ToString();
            }
            catch { }
            try
            {
                if (row["NgayNhap"] != DBNull.Value)
                    objPHIEU_DAT_HANG.NgayNhap = Convert.ToDateTime((row["NgayNhap"]));
            }
            catch { }
            try
            {
                if (row["NguoiNhap"] != DBNull.Value)
                    objPHIEU_DAT_HANG.NguoiNhap = (row["NguoiNhap"].ToString());
            }
            catch { }
            try
            {
                if (row["PlantReferenceSAP"] != DBNull.Value)
                    objPHIEU_DAT_HANG.PlanReferenceSAP = row["PlantReferenceSAP"].ToString();
            }
            catch { }
            try
            {
                if (row["TinhTrang"] != DBNull.Value)
                    objPHIEU_DAT_HANG.TinhTrang = int.Parse(row["TinhTrang"].ToString());
            }
            catch { }
            try
            {
                if (row["LoaiPO"] != DBNull.Value)
                    objPHIEU_DAT_HANG.LoaiPO = (row["LoaiPO"].ToString());
            }
            catch { }
            try
            {
                if (row["PlantGui"] != DBNull.Value)
                    objPHIEU_DAT_HANG.PlantGui = (row["PlantGui"].ToString());
            }
            catch { }
            

            return objPHIEU_DAT_HANG;
        }
        private List<PHIEU_DAT_HANG> GetPHIEU_DAT_HANG(DataTable dt)
        {
            List<PHIEU_DAT_HANG> lst = new List<PHIEU_DAT_HANG>();
            PHIEU_DAT_HANG obj;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                obj = this.GetDataRow(dt.Rows[i]);
                lst.Add(obj);
            }
            return lst;
        }
        #endregion Private Method

        #region Public methods
        public List<PHIEU_DAT_HANG> usp_SelectPHIEU_DAT_HANG(string POThamChieuSAP)
        {
            string spName = "usp_SelectPHIEU_DAT_HANG";
            DataTable dt = new DataTable();
            List<PHIEU_DAT_HANG> lst = new List<PHIEU_DAT_HANG>();
            try
            {
                SqlHelper.OpenConnection();

                SqlParameter[] par = new SqlParameter[1];
                par[0] = new SqlParameter("@POThamChieuSAP", POThamChieuSAP);

                if (!SqlHelper.IsHaveTran())
                    dt = SqlHelper.ExecuteToDataTable(spName, par);
                else
                {
                    dt = SqlHelper.ExecuteToDataTable(SqlHelper.Tran, spName, par);
                }

                lst = this.GetPHIEU_DAT_HANG(dt);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }
            return lst;
        }

        public List<PHIEU_DAT_HANG> usp_SelectPHIEU_DAT_HANG_MAX(string matau)
        {
            //string spName = "SELECT TOP 1 * FROM PHIEU_DAT_HANG INNER JOIN TAU ON PHIEU_DAT_HANG.PlantReferenceSAP=TAU.PlantReferenceSAP WHERE TenTau2KiTu='" + matau + "' ORDER BY POTHAMCHIEUSAP DESC";
            string spName = "SELECT TOP 1 * FROM PHIEU_DAT_HANG ORDER BY POTHAMCHIEUSAP DESC";
            DataTable dt = new DataTable();
            List<PHIEU_DAT_HANG> lst = new List<PHIEU_DAT_HANG>();
            try
            {
                SqlHelper.OpenConnection();              

                if (!SqlHelper.IsHaveTran())
                    dt = SqlHelper.ExecuteToDataTable(spName, CommandType.Text);
                else
                {
                    dt = SqlHelper.ExecuteToDataTable(SqlHelper.Tran, spName, CommandType.Text);
                }

                lst = this.GetPHIEU_DAT_HANG(dt);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }
            return lst;
        }

        public List<PHIEU_DAT_HANG> SelectPHIEU_DAT_HANGsSearch(PHIEU_DAT_HANG obj, int IsNhapKho)
        {
            string spName = "usp_SelectPHIEU_DAT_HANGsSearch";
            DataTable dt = new DataTable();
            List<PHIEU_DAT_HANG> lst = new List<PHIEU_DAT_HANG>();
            
            try
            {
                
                SqlHelper.OpenConnection();

                SqlParameter[] par = new SqlParameter[6];
                par[0] = new SqlParameter("@POThamChieuSAP", obj.POThamChieuSAP == "" ? null : obj.POThamChieuSAP);
                if (obj.NgayNhap.Year < 2012)
                    par[1] = new SqlParameter("@NgayTao", null);
                else
                    par[1] = new SqlParameter("@NgayTao", obj.NgayNhap);
                par[2] = new SqlParameter("@NguoiTao", obj.NguoiNhap == "" ? null : obj.NguoiNhap);
                if (obj.TinhTrang == -1)
                    par[3] = new SqlParameter("@TinhTrang", null);
                else
                    par[3] = new SqlParameter("@TinhTrang", obj.TinhTrang);


                if (obj.NgayNhapTo.Year < 2012)
                    par[4] = new SqlParameter("@NgayTaoTo", null);
                else
                    par[4] = new SqlParameter("@NgayTaoTo", obj.NgayNhapTo);

                par[5] = new SqlParameter("@IsNhapKho", IsNhapKho);

                if (!SqlHelper.IsHaveTran())
                    dt = SqlHelper.ExecuteToDataTable(spName, par);
                else
                {
                    dt = SqlHelper.ExecuteToDataTable(SqlHelper.Tran, spName, par);
                }
                lst = this.GetPHIEU_DAT_HANG(dt);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }

            return lst;
        }

        public int usp_InsertPHIEU_DAT_HANG(PHIEU_DAT_HANG obj)
        {
            string spName = "usp_InsertPHIEU_DAT_HANG";
            int iResult = 0;
            try
            {
                SqlParameter[] par = new SqlParameter[6];
                par[0] = new SqlParameter("@POThamChieuSAP", obj.POThamChieuSAP);
                par[1] = new SqlParameter("@NgayNhap", obj.NgayNhap);
                par[2] = new SqlParameter("@NguoiNhap", obj.NguoiNhap);
                par[3] = new SqlParameter("@PlanReferenceSAP", obj.PlanReferenceSAP);
                par[4] = new SqlParameter("@TinhTrang", obj.TinhTrang);
                par[5] = new SqlParameter("@LoaiPO", obj.LoaiPO);


                if (SqlHelper.IsHaveTran())
                    iResult = SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);
                else
                    iResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, spName, par);


            }
            catch (Exception ex)
            {
            }
            return iResult;
        }

        public int usp_UpdatePHIEU_DAT_HANG(PHIEU_DAT_HANG obj)
        {
            string spName = "usp_UpdatePHIEU_DAT_HANG";
            int iResult = 0;
            try
            {
                SqlParameter[] par = new SqlParameter[5];
                par[0] = new SqlParameter("@POThamChieuSAP", obj.POThamChieuSAP);
                par[1] = new SqlParameter("@NgayTao", obj.NgayNhap);
                par[2] = new SqlParameter("@NguoiTao", obj.NguoiNhap);
                par[3] = new SqlParameter("@PlanReferenceSAP", obj.PlanReferenceSAP);
                par[4] = new SqlParameter("@TinhTrang", obj.TinhTrang);


                if (SqlHelper.IsHaveTran())
                    iResult = SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);
                else
                    iResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, spName, par);


            }
            catch (Exception ex)
            {
            }
            return iResult;
        }

        public int usp_DeletePHIEU_DAT_HANG(string POThamChieuSAP)
        {
            int iResult = 0;
            string spName = "usp_DeletePHIEU_DAT_HANG";

            try
            {
                SqlParameter[] par = new SqlParameter[8];
                par[0] = new SqlParameter("@POThamChieuSAP", POThamChieuSAP);


                if (SqlHelper.IsHaveTran())
                    iResult = SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);
                else
                    iResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, spName, par);

            }
            catch (Exception ex)
            {
            }
            return iResult;
        }

        public int InsertPHIEU_DAT_HANG(PHIEU_DAT_HANG obj, List<CHI_TIET_PDH> lstCT)
        {
            string spName = "";
            SqlParameter[] par;
            int iResult = 0;
            try
            {
                SqlHelper.BeginTran();
                spName = "usp_InsertPHIEU_DAT_HANG";
                par = new SqlParameter[8];
                par[0] = new SqlParameter("@RETURNVALUE", SqlDbType.Int);
                par[0].Direction = ParameterDirection.ReturnValue;
                par[1] = new SqlParameter("@POThamChieuSAP", obj.POThamChieuSAP);
                par[2] = new SqlParameter("@NgayNhap", obj.NgayNhap);
                par[3] = new SqlParameter("@NguoiNhap", obj.NguoiNhap);
                par[4] = new SqlParameter("@PlanReferenceSAP", obj.PlanReferenceSAP);
                par[5] = new SqlParameter("@TinhTrang", obj.TinhTrang);
                par[6] = new SqlParameter("@LoaiPO", obj.LoaiPO);
                par[7] = new SqlParameter("@PlantGui", obj.PlantGui);
                
                
                SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);
                iResult = (int)par[0].Value;

                if (iResult < 0)
                    SqlHelper.Tran.Rollback();
                else
                {
                    if (obj.TinhTrang == 0) // nếu cả PO không bị delete - căn cứ vào header của PO template
                    {
                        int i = 0;
                        string mapyc = "";
                        for (i = 0; i < lstCT.Count; i++)
                        {
                            // thêm mới, update, đánh dấu xóa CHI_TIET_PDH
                            spName = "usp_InsertCHI_TIET_PDH";
                            par = new SqlParameter[14];
                            par[0] = new SqlParameter("@RETURNVALUE", SqlDbType.Int);
                            par[0].Direction = ParameterDirection.ReturnValue;
                            par[1] = new SqlParameter("@MaChiTiet", lstCT[i].MaChiTiet);
                            par[2] = new SqlParameter("@POThamChieuSAP", lstCT[i].POThamChieuSAP);
                            par[3] = new SqlParameter("@MaThietBi", lstCT[i].MaThietBi);
                            par[4] = new SqlParameter("@SoLuong", lstCT[i].SoLuong);
                            par[5] = new SqlParameter("@TinhTrang", lstCT[i].TinhTrang);
                            par[6] = new SqlParameter("@NgayGiaoHang", lstCT[i].NgayGiaoHang);
                            par[7] = new SqlParameter("@NguoiTao", lstCT[i].NguoiTao);
                            par[8] = new SqlParameter("@NgayTao", lstCT[i].NgayTao);
                            par[9] = new SqlParameter("@MaTrangThai", lstCT[i].MaTrangThai);
                            par[10] = new SqlParameter("@POItemNo", lstCT[i].POItem_No);
                            par[11] = new SqlParameter("@AccAssignment", lstCT[i].AccAssignment);
                            par[12] = new SqlParameter("@MaTaiSan", lstCT[i].MaTaiSan);
                            par[13] = new SqlParameter("@MaPhieuYeuCau", lstCT[i].MaPhieuYeuCau);

                            SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);
                            iResult = (int)par[0].Value;


                            if (iResult <= 0)
                            {
                                SqlHelper.Tran.Rollback();
                                break;
                            }
                            else
                            {
                                lstCT[i].MaChiTiet = iResult;                                

                                if (lstCT[i].TinhTrang == 0)
                                {
                                    if (lstCT[i].ItemCategory == "L") // gia cong
                                    {

                                        // XÓA DỮ LIỆU TRƯỚC KHI INSERT VÀO CHI_TIET_PDH_COMPONENT
                                        spName = "usp_DeleteCHI_TIET_PDH_COMPONENTByMaChiTietPDH";
                                        par = new SqlParameter[1];
                                        par[0] = new SqlParameter("@MaChiTietPDH", lstCT[i].MaChiTiet);
                                        iResult = SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);
                                        if (iResult >= 0)
                                        {
                                            // INSERT VÀO CHI_TIET_PDH_COMPONENT
                                            long kq = 0;
                                            int c = 0;
                                            for (c = 0; c < lstCT[i].lstComponent.Count; c++)
                                            {
                                                lstCT[i].lstComponent[c].MaChiTietPDH = lstCT[i].MaChiTiet;

                                                spName = "usp_InsertCHI_TIET_PDH_COMPONENT";

                                                par = new SqlParameter[5];
                                                par[0] = new SqlParameter("@MaChiTietPDH", lstCT[i].lstComponent[c].MaChiTietPDH);
                                                par[1] = new SqlParameter("@MaThietBi", lstCT[i].lstComponent[c].MaThietBi);
                                                par[2] = new SqlParameter("@SoLuong", lstCT[i].lstComponent[c].SoLuong);
                                                par[3] = new SqlParameter("@Plant", lstCT[i].lstComponent[c].Plant);
                                                par[4] = new SqlParameter("@MaComponent", SqlDbType.BigInt);
                                                par[4].Direction = ParameterDirection.Output;

                                                SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);

                                                kq = (long)par[4].Value;
                                                if (kq <= 0)
                                                    break;

                                            }
                                            if (c < lstCT[i].lstComponent.Count)
                                            {
                                                iResult = 0;
                                                SqlHelper.Tran.Rollback();
                                                break;
                                            }
                                        }
                                        else
                                        {
                                            iResult = 0;
                                            SqlHelper.Tran.Rollback();
                                            break;
                                        }
                                    }
                                    // INSERT VÀO BẢNG CHI_TIET_PDH_PYC
                                    if (!mapyc.Contains(lstCT[i].MaPhieuYeuCau))
                                    {
                                        spName = "usp_InsertCHI_TIET_PDH_PYC";
                                        par = new SqlParameter[3];
                                        par[0] = new SqlParameter("@RETURNVALUE", SqlDbType.Int);
                                        par[0].Direction = ParameterDirection.ReturnValue;
                                        par[1] = new SqlParameter("@POThamChieuSAP", lstCT[i].POThamChieuSAP);
                                        par[2] = new SqlParameter("@MaPYC", lstCT[i].MaPhieuYeuCau);

                                        SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);
                                        iResult = (int)par[0].Value;
                                        if (iResult <= 0)
                                        {
                                            SqlHelper.Tran.Rollback();
                                            break;
                                        }
                                    }
                                    mapyc += lstCT[i].MaPhieuYeuCau + "#";
                                }
                            }
                        }
                        if (i == lstCT.Count && i > 0)
                        {
                            SqlHelper.CommitTran();
                            iResult = 1;
                        }
                    }
                    else 
                        SqlHelper.CommitTran();
                }
                


            }
            catch (Exception ex)
            {
                SqlHelper.Tran.Rollback();
                iResult = 0;
            }
            finally
            {
                 SqlHelper.CloseConnection();
            }
            return iResult;
        }

        #endregion Public methods
    }
}
